﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;

namespace Data_Access_Tier
{
    public class DIC_HOLIDAYData : DatabaseObjectBase
    {
        public DIC_HOLIDAYData()
        {
            Initialize(true);
        }
        public DIC_HOLIDAYData(Boolean newConnection)
        {
            Initialize(newConnection);
        }

        public Int32 DIC_HOLIDAY_Insert(Guid HolidayID, String HolidayName, DateTime FromDate, DateTime ToDate, String Description)
        {
            try
            {
                if (_dbconn.State != ConnectionState.Open)
                    _dbconn.Open();
                _dbadapter.InsertCommand = new SqlCommand("DIC_HOLIDAY_Insert");
                _dbadapter.InsertCommand.CommandType = CommandType.StoredProcedure;
                _dbadapter.InsertCommand.Connection = _dbconn;
                _trans = _dbconn.BeginTransaction();
                _dbadapter.InsertCommand.Transaction = _trans;
                _dbadapter.InsertCommand.Parameters.Clear();
                _dbadapter.InsertCommand.Parameters.Add(new SqlParameter("@HolidayID", SqlDbType.UniqueIdentifier));
                _dbadapter.InsertCommand.Parameters.Add(new SqlParameter("@HolidayName", SqlDbType.NVarChar, 100));
                _dbadapter.InsertCommand.Parameters.Add(new SqlParameter("@FromDate", SqlDbType.DateTime));
                _dbadapter.InsertCommand.Parameters.Add(new SqlParameter("@ToDate", SqlDbType.DateTime));
                _dbadapter.InsertCommand.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar, 255));
                _dbadapter.InsertCommand.Prepare();
                SqlGuid HolidayIDSQL = HolidayID;
                _dbadapter.InsertCommand.Parameters["@HolidayID"].Value = HolidayIDSQL;
                SqlString HolidayNameSQL = HolidayName;
                _dbadapter.InsertCommand.Parameters["@HolidayName"].Value = HolidayNameSQL;
                SqlDateTime FromDateSQL = SqlDateTime.Null;
                if (FromDate > DateTime.MinValue)
                {
                    try
                    {
                        FromDateSQL = FromDate;
                    }
                    catch (Exception eFromDate)
                    {
                        String FromDateMessage = eFromDate.Message;
                        FromDateSQL = SqlDateTime.Null;
                    }
                }
                _dbadapter.InsertCommand.Parameters["@FromDate"].Value = FromDateSQL;
                SqlDateTime ToDateSQL = SqlDateTime.Null;
                if (ToDate > DateTime.MinValue)
                {
                    try
                    {
                        ToDateSQL = ToDate;
                    }
                    catch (Exception eToDate)
                    {
                        String ToDateMessage = eToDate.Message;
                        ToDateSQL = SqlDateTime.Null;
                    }
                }
                _dbadapter.InsertCommand.Parameters["@ToDate"].Value = ToDateSQL;
                SqlString DescriptionSQL = Description;
                _dbadapter.InsertCommand.Parameters["@Description"].Value = DescriptionSQL;
                Int32 returnvalue = Convert.ToInt32(_dbadapter.InsertCommand.ExecuteScalar());

                _trans.Commit();
                return returnvalue;
            }
            catch (SqlException e)
            {
                try
                {
                    _trans.Rollback();
                }
                catch (SqlException ex)
                {
                    throw new Exception("SQL Rollback Error: DIC_HOLIDAY_Insert: " + ex.Message, ex);
                }
                throw new Exception("SQL Error: DIC_HOLIDAY_Insert: " + e.Message, e);
            }
            finally
            {
                if (_dbadapter.InsertCommand != null)
                    _dbadapter.InsertCommand.Dispose();
                CloseConnection();
            }
        }
        public Int32 DIC_HOLIDAY_Update(Guid HolidayID, String HolidayName, DateTime FromDate, DateTime ToDate, String Description)
        {
            try
            {
                if (_dbconn.State != ConnectionState.Open)
                    _dbconn.Open();
                _dbadapter.UpdateCommand = new SqlCommand("DIC_HOLIDAY_Update");
                _dbadapter.UpdateCommand.CommandType = CommandType.StoredProcedure;
                _dbadapter.UpdateCommand.Connection = _dbconn;

                _trans = _dbconn.BeginTransaction();
                _dbadapter.UpdateCommand.Transaction = _trans;

                _dbadapter.UpdateCommand.Parameters.Clear();
                _dbadapter.UpdateCommand.Parameters.Add(new SqlParameter("@HolidayID", SqlDbType.UniqueIdentifier));
                _dbadapter.UpdateCommand.Parameters.Add(new SqlParameter("@HolidayName", SqlDbType.NVarChar, 100));
                _dbadapter.UpdateCommand.Parameters.Add(new SqlParameter("@FromDate", SqlDbType.DateTime));
                _dbadapter.UpdateCommand.Parameters.Add(new SqlParameter("@ToDate", SqlDbType.DateTime));
                _dbadapter.UpdateCommand.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar, 255));
                _dbadapter.UpdateCommand.Prepare();
                SqlGuid HolidayIDSQL = HolidayID;
                _dbadapter.UpdateCommand.Parameters["@HolidayID"].Value = HolidayIDSQL;
                SqlString HolidayNameSQL = HolidayName;
                _dbadapter.UpdateCommand.Parameters["@HolidayName"].Value = HolidayNameSQL;
                SqlDateTime FromDateSQL = SqlDateTime.Null;
                if (FromDate > DateTime.MinValue)
                {
                    try
                    {
                        FromDateSQL = FromDate;
                    }
                    catch (Exception eFromDate)
                    {
                        String FromDateMessage = eFromDate.Message;
                        FromDateSQL = SqlDateTime.Null;
                    }
                }
                _dbadapter.UpdateCommand.Parameters["@FromDate"].Value = FromDateSQL;
                SqlDateTime ToDateSQL = SqlDateTime.Null;
                if (ToDate > DateTime.MinValue)
                {
                    try
                    {
                        ToDateSQL = ToDate;
                    }
                    catch (Exception eToDate)
                    {
                        String ToDateMessage = eToDate.Message;
                        ToDateSQL = SqlDateTime.Null;
                    }
                }
                _dbadapter.UpdateCommand.Parameters["@ToDate"].Value = ToDateSQL;
                SqlString DescriptionSQL = Description;
                _dbadapter.UpdateCommand.Parameters["@Description"].Value = DescriptionSQL;
                Int32 returnvalue = Convert.ToInt32(_dbadapter.UpdateCommand.ExecuteScalar());

                _trans.Commit();
                return returnvalue;
            }
            catch (SqlException e)
            {
                try
                {
                    _trans.Rollback();
                }
                catch (SqlException ex)
                {
                    throw new Exception("SQL Rollback Error: DIC_HOLIDAY_Update: " + ex.Message, ex);
                }
                throw new Exception("SQL Error: DIC_HOLIDAY_Update: " + e.Message, e);
            }
            finally
            {
                if (_dbadapter.UpdateCommand != null)
                    _dbadapter.UpdateCommand.Dispose();
                CloseConnection();
            }
        }
        public Int32 DIC_HOLIDAY_Disable(Guid HolidayID, ref Boolean Enabled)
        {
            try
            {
                if (_dbconn.State != ConnectionState.Open)
                    _dbconn.Open();
                _dbadapter.UpdateCommand = new SqlCommand("DIC_HOLIDAY_Disable");
                _dbadapter.UpdateCommand.CommandType = CommandType.StoredProcedure;
                _dbadapter.UpdateCommand.Connection = _dbconn;

                _trans = _dbconn.BeginTransaction();
                _dbadapter.UpdateCommand.Transaction = _trans;

                _dbadapter.UpdateCommand.Parameters.Clear();
                _dbadapter.UpdateCommand.Parameters.Add(new SqlParameter("@HolidayID", SqlDbType.UniqueIdentifier));
                _dbadapter.UpdateCommand.Parameters.Add(new SqlParameter("@enabled", SqlDbType.Bit));
                _dbadapter.UpdateCommand.Prepare();
                SqlGuid HolidayIDSQL = HolidayID;
                _dbadapter.UpdateCommand.Parameters["@HolidayID"].Value = HolidayIDSQL;
                _dbadapter.UpdateCommand.Parameters["@enabled"].Direction = ParameterDirection.Output;
                Int32 returnvalue = Convert.ToInt32(_dbadapter.UpdateCommand.ExecuteScalar());

                _trans.Commit();
                return returnvalue;
            }
            catch (SqlException e)
            {
                try
                {
                    _trans.Rollback();
                }
                catch (SqlException ex)
                {
                    throw new Exception("SQL Rollback Error: DIC_HOLIDAY_Disable: " + ex.Message, ex);
                }
                throw new Exception("SQL Error: DIC_HOLIDAY_Disable: " + e.Message, e);
            }
            finally
            {
                if (_dbadapter.UpdateCommand != null)
                    _dbadapter.UpdateCommand.Dispose();
                CloseConnection();
            }
        }
        public Int32 DIC_HOLIDAY_Delete(Guid HolidayID)
        {
            try
            {
                if (_dbconn.State != ConnectionState.Open)
                    _dbconn.Open();
                _dbadapter.DeleteCommand = new SqlCommand("DIC_HOLIDAY_Delete");
                _dbadapter.DeleteCommand.CommandType = CommandType.StoredProcedure;
                _dbadapter.DeleteCommand.Connection = _dbconn;

                _trans = _dbconn.BeginTransaction();
                _dbadapter.DeleteCommand.Transaction = _trans;

                _dbadapter.DeleteCommand.Parameters.Clear();
                _dbadapter.DeleteCommand.Parameters.Add(new SqlParameter("@HolidayID", SqlDbType.UniqueIdentifier));
                _dbadapter.DeleteCommand.Prepare();
                SqlGuid HolidayIDSQL = HolidayID;
                _dbadapter.DeleteCommand.Parameters["@HolidayID"].Value = HolidayIDSQL;
                Int32 returnvalue = Convert.ToInt32(_dbadapter.DeleteCommand.ExecuteScalar());

                _trans.Commit();
                return returnvalue;
            }
            catch (SqlException e)
            {
                try
                {
                    _trans.Rollback();
                }
                catch (SqlException ex)
                {
                    throw new Exception("SQL Rollback Error: DIC_HOLIDAY_Delete: " + ex.Message, ex);
                }
                throw new Exception("SQL Error: DIC_HOLIDAY_Delete: " + e.Message, e);
            }
            finally
            {
                if (_dbadapter.DeleteCommand != null)
                    _dbadapter.DeleteCommand.Dispose();
                CloseConnection();
            }
        }
        public Int32 DIC_HOLIDAY_Save(Guid HolidayID, String HolidayName, DateTime FromDate, DateTime ToDate, String Description)
        {
            try
            {
                if (_dbconn.State != ConnectionState.Open)
                    _dbconn.Open();
                _dbadapter.UpdateCommand = new SqlCommand("DIC_HOLIDAY_Save");
                _dbadapter.UpdateCommand.CommandType = CommandType.StoredProcedure;
                _dbadapter.UpdateCommand.Connection = _dbconn;

                _trans = _dbconn.BeginTransaction();
                _dbadapter.UpdateCommand.Transaction = _trans;

                _dbadapter.UpdateCommand.Parameters.Clear();
                _dbadapter.UpdateCommand.Parameters.Add(new SqlParameter("@HolidayID", SqlDbType.UniqueIdentifier));
                _dbadapter.UpdateCommand.Parameters.Add(new SqlParameter("@HolidayName", SqlDbType.NVarChar, 100));
                _dbadapter.UpdateCommand.Parameters.Add(new SqlParameter("@FromDate", SqlDbType.DateTime));
                _dbadapter.UpdateCommand.Parameters.Add(new SqlParameter("@ToDate", SqlDbType.DateTime));
                _dbadapter.UpdateCommand.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar, 255));
                _dbadapter.UpdateCommand.Prepare();
                SqlGuid HolidayIDSQL = HolidayID;
                _dbadapter.UpdateCommand.Parameters["@HolidayID"].Value = HolidayIDSQL;
                SqlString HolidayNameSQL = HolidayName;
                _dbadapter.UpdateCommand.Parameters["@HolidayName"].Value = HolidayNameSQL;
                SqlDateTime FromDateSQL = SqlDateTime.Null;
                if (FromDate > DateTime.MinValue)
                {
                    try
                    {
                        FromDateSQL = FromDate;
                    }
                    catch (Exception eFromDate)
                    {
                        String FromDateMessage = eFromDate.Message;
                        FromDateSQL = SqlDateTime.Null;
                    }
                }
                _dbadapter.UpdateCommand.Parameters["@FromDate"].Value = FromDateSQL;
                SqlDateTime ToDateSQL = SqlDateTime.Null;
                if (ToDate > DateTime.MinValue)
                {
                    try
                    {
                        ToDateSQL = ToDate;
                    }
                    catch (Exception eToDate)
                    {
                        String ToDateMessage = eToDate.Message;
                        ToDateSQL = SqlDateTime.Null;
                    }
                }
                _dbadapter.UpdateCommand.Parameters["@ToDate"].Value = ToDateSQL;
                SqlString DescriptionSQL = Description;
                _dbadapter.UpdateCommand.Parameters["@Description"].Value = DescriptionSQL;
                Int32 returnvalue = Convert.ToInt32(_dbadapter.UpdateCommand.ExecuteScalar());

                _trans.Commit();
                return returnvalue;
            }
            catch (SqlException e)
            {
                try
                {
                    _trans.Rollback();
                }
                catch (SqlException ex)
                {
                    throw new Exception("SQL Rollback Error: DIC_HOLIDAY_Save: " + ex.Message, ex);
                }
                throw new Exception("SQL Error: DIC_HOLIDAY_Save: " + e.Message, e);
            }
            finally
            {
                if (_dbadapter.UpdateCommand != null)
                    _dbadapter.UpdateCommand.Dispose();
                CloseConnection();
            }
        }
        public DataSet DIC_HOLIDAY_GetList()
        {
            try
            {
                if (_dbconn.State != ConnectionState.Open)
                    _dbconn.Open();
                _dbadapter.SelectCommand = new SqlCommand("DIC_HOLIDAY_GetList");
                _dbadapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _dbadapter.SelectCommand.Connection = _dbconn;
                _dbadapter.SelectCommand.Parameters.Clear();
                _dbadapter.SelectCommand.Prepare();
                _dbadapter.Fill(_data);

                return _data;
            }
            catch (SqlException e)
            {
                throw new Exception("SQL Error: DIC_HOLIDAY_GetList: " + e.Message, e);
            }
            finally
            {
                if (_dbadapter.SelectCommand != null)
                    _dbadapter.SelectCommand.Dispose();
                CloseConnection();
            }
        }
        public DataSet DIC_HOLIDAY_Get(Guid HolidayID)
        {
            try
            {
                if (_dbconn.State != ConnectionState.Open)
                    _dbconn.Open();
                _dbadapter.SelectCommand = new SqlCommand("DIC_HOLIDAY_Get");
                _dbadapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                _dbadapter.SelectCommand.Connection = _dbconn;
                _dbadapter.SelectCommand.Parameters.Clear();
                _dbadapter.SelectCommand.Parameters.Add(new SqlParameter("@HolidayID", SqlDbType.UniqueIdentifier));
                _dbadapter.SelectCommand.Prepare();
                SqlGuid HolidayIDSQL = HolidayID;
                _dbadapter.SelectCommand.Parameters["@HolidayID"].Value = HolidayIDSQL;
                _dbadapter.Fill(_data);

                return _data;
            }
            catch (SqlException e)
            {
                throw new Exception("SQL Error: DIC_HOLIDAY_Get: " + e.Message, e);
            }
            finally
            {
                if (_dbadapter.SelectCommand != null)
                    _dbadapter.SelectCommand.Dispose();
                CloseConnection();
            }
        }
    }
}
